﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Data.OracleClient;
using System.Data.OleDb;

namespace DBUtility
{
    internal abstract class DBHelper:IDBHelper
    {
        protected static DbConnection con;
        private string dataBaseType = String.Empty;
        public void CreateConnection()
        {
            string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConString"].ToString();
            if (dataBaseType == String.Empty)
                dataBaseType = GetdbTypeFromConfig();
            switch (dataBaseType)
            {
                case "MySql.Data.MySqlClient":
                    con = new MySqlConnection(conStr);
                    break;
                case "System.Data.SqlClient":
                    con = new SqlConnection(conStr);
                    break;
                case "System.Data.OracleClient":
                    con = new OracleConnection(conStr);
                    break;
                case "System.Data.OleDb":
                    con = new OleDbConnection(conStr);
                    break;
                default :
                    con = new SqlConnection(conStr);
                    break;
            }
        }
        public abstract int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] cmdParams);

        public abstract int ExecuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParams);

        public abstract DataSet ExecuteQuery(CommandType cmdType, string cmdText, params DbParameter[] cmdParams);

        public abstract DataSet ExecuteQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParams);

        public abstract DataTable ExecuteNQuery(CommandType cmdType, string cmdText, params DbParameter[] cmdParams);

        public abstract DataTable ExecuteNQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParams);

        public abstract List<List<string>> ExecuteLQuery(CommandType cmdType, string cmdText, params DbParameter[] cmdParams);

        public abstract List<List<string>> ExecuteLQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParams);

        public abstract DbDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] cmdParams);

        public abstract DbDataReader ExecuteReader(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParams);

        public abstract object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] cmdParams);

        public abstract object ExecuteScalar(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParams);

        /// <summary>
        /// 从配置节读出使用的数据库类型
        /// </summary>
        /// <returns></returns>
        private string GetdbTypeFromConfig()
        {
            return System.Configuration.ConfigurationManager.ConnectionStrings["ConString"].ProviderName.Trim();
        }

        public DbParameter CreateParam(string paramName, DbType dbType, object value, int size, ParameterDirection direction)
        {
            DbParameter param;

            //添加
            if(dataBaseType==String.Empty)
                dataBaseType = GetdbTypeFromConfig();

            param = new MySqlParameter(paramName, dbType);
            switch (dataBaseType)
            {
                case "MySql.Data.MySqlClient":
                    param = new MySqlParameter(paramName, dbType);
                    break;
                case "System.Data.SqlClient":
                    param = new SqlParameter(paramName, dbType);
                    break;
                case "System.Data.OracleClient":
                    param = new OracleParameter(paramName, dbType);
                    break;
                case "System.Data.OleDb":
                    param = new OleDbParameter(paramName, dbType);
                    break;
                default:
                    param = new SqlParameter(paramName, dbType);
                    break;
            }
            if (value != null)
                param.Value = value;
            if (size > 0)
                param.Size = size;
            param.DbType = dbType;
            param.Direction = direction;
            return param;
        }

        public DbParameter CreateInParam(string paramName, DbType dbType, object value, int size)
        {
            return CreateParam(paramName, dbType, value, size, ParameterDirection.Input);
        }

        public DbParameter CreateInParam(string paramName, DbType dbType, object value)
        {
            return CreateParam(paramName, dbType, value, 0, ParameterDirection.Input);
        }

        public DbParameter CreateOutParam(string paramName, DbType dbType, int size)
        {
            return CreateParam(paramName, dbType, null, size, ParameterDirection.Output);
        }

        public DbParameter CreateOutParam(string paramName, DbType dbType)
        {
            return CreateParam(paramName, dbType, null, 0, ParameterDirection.Output);
        }

        public DbParameter CreateReturnParam(string paramName, DbType dbType, int size)
        {
            return CreateParam(paramName, dbType, null, size, ParameterDirection.ReturnValue);
        }

        public DbParameter CreateReturnParam(string paramName, DbType dbType)
        {
            return CreateParam(paramName, dbType, null, 0, ParameterDirection.ReturnValue);
        }

        /// <summary>
        /// 获取一个Command对象
        /// </summary>
        /// <param name="con">数据库的连接</param>
        /// <param name="cmd">表示要对数据源执行的Transact-SQL语句或存储过程</param>
        /// <param name="cmdType">命令字符串类型</param>
        /// <param name="cmdText">执行语句</param>
        /// <param name="trans">事务</param>
        /// <param name="cmdParams">命令参数列表</param>
        /// <returns>返回Command对象</returns>
        public void PrepareCmd(DbCommand cmd, CommandType cmdType, string cmdText, DbTransaction trans, params DbParameter[] cmdParams)
        {
            if (con == null)
                CreateConnection();

            if (con.State != ConnectionState.Open)
                con.Open();

            cmd.Connection = con;
            cmd.CommandType = cmdType;

            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            if (cmdParams != null)
            {
                cmd.Parameters.AddRange(cmdParams);
            }
        }
    }
}
